﻿using Dapper;
using System;
using System.Data;
using System.Text;
using System.Collections.Generic;
using System.Data.SqlClient;
using WeiXinPF.DBUtility;
using WeiXinPF.Common;
using System.Linq;


namespace WeiXinPF.DAL
{
    /// <summary>
    /// 数据访问类:advertisement
    /// </summary>
    public partial class advertisement
    {

        private string databaseprefix; //数据库表名前缀

        public advertisement()
        {
            databaseprefix = "wx_";
        }
        public advertisement(string _databaseprefix)
        {
            databaseprefix = _databaseprefix;
        }


        public void Add(Model.advertisement model)
        {
            using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        StringBuilder strSql = new StringBuilder();
                        strSql.Append("insert into "+ databaseprefix+ "advertisement(wid,title,categoryid,img_path,linkurl,context,addtime,beginTime,endtime,isdelete,remark,ordernumber) values(");
                        strSql.Append("@wid,");
                        strSql.Append("@title,");
                        strSql.Append("@categoryid,");
                        strSql.Append("@img_path,");
                        strSql.Append("@linkurl,");
                        strSql.Append("@context,");
                        strSql.Append("@addtime,");
                        strSql.Append("@beginTime,");
                        strSql.Append("@endtime,");
                        strSql.Append("@isdelete,");
                        strSql.Append("@remark,");
                        strSql.Append("@ordernumber)");
                        SqlParameter[] parameters = {
                                new SqlParameter("@wid", SqlDbType.Int,4),
                                new SqlParameter("@categoryid", SqlDbType.Int,4),
                                new SqlParameter("@title", SqlDbType.NVarChar,100),
                                new SqlParameter("@img_path", SqlDbType.NVarChar,200),
                                new SqlParameter("@linkurl", SqlDbType.NVarChar,200),
                                new SqlParameter("@context", SqlDbType.NVarChar,500),
                                new SqlParameter("@addTime", SqlDbType.DateTime),
                                new SqlParameter("@beginTime", SqlDbType.DateTime),
                                new SqlParameter("@endtime", SqlDbType.DateTime),
                                new SqlParameter("@isdelete", SqlDbType.Bit),
                                new SqlParameter("@remark", SqlDbType.NVarChar,500),
                                new SqlParameter("@ordernumber", SqlDbType.Int,4)};
                        parameters[0].Value = model.wid;
                        parameters[1].Value = model.categoryid;
                        parameters[2].Value = model.title;
                        parameters[3].Value = model.img_path;
                        parameters[4].Value = model.linkurl;
                        parameters[5].Value = model.context;
                        parameters[6].Value = model.addtime;
                        parameters[7].Value = model.begintime;
                        parameters[8].Value = model.endtime;
                        parameters[9].Value = model.isdelete;
                        parameters[10].Value = model.remark;
                        parameters[11].Value = model.ordernumber;
                        DbHelperSQL.ExecuteSql(conn, trans, strSql.ToString(), parameters);
                        trans.Commit();
                        conn.Close();
                    }
                    catch (Exception ex)
                    {

                    }
                }
            }
        }

        public Model.advertisement getModel(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 * from " + databaseprefix + "Advertisement ");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
                    new SqlParameter("@id", SqlDbType.Int,4)};
            parameters[0].Value = id;

            Model.advertisement model = new Model.advertisement();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["id"].ToString() != "")
                {
                    model.id = int.Parse(ds.Tables[0].Rows[0]["id"].ToString());
                }
                if (ds.Tables[0].Rows[0]["isdelete"].ToString() != "")
                {
                    model.isdelete = ds.Tables[0].Rows[0]["isdelete"].ToString().ToLower()=="true"? true:false;
                }
                if (ds.Tables[0].Rows[0]["wid"].ToString() != "")
                {
                    model.wid = int.Parse(ds.Tables[0].Rows[0]["wid"].ToString());
                }
                if (ds.Tables[0].Rows[0]["categoryid"].ToString() != "")
                {
                    model.categoryid = int.Parse(ds.Tables[0].Rows[0]["categoryid"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ordernumber"].ToString() != "")
                {
                    model.ordernumber = int.Parse(ds.Tables[0].Rows[0]["ordernumber"].ToString());
                }
                model.title = ds.Tables[0].Rows[0]["title"].ToString();
                model.img_path = ds.Tables[0].Rows[0]["img_path"].ToString();
                model.linkurl = ds.Tables[0].Rows[0]["linkurl"].ToString();
                model.context = ds.Tables[0].Rows[0]["context"].ToString();
                model.remark = ds.Tables[0].Rows[0]["remark"].ToString();
                if (ds.Tables[0].Rows[0]["addtime"].ToString() != "")
                {
                    model.addtime = DateTime.Parse(ds.Tables[0].Rows[0]["addtime"].ToString());
                }
                if (ds.Tables[0].Rows[0]["begintime"].ToString() != "")
                {
                    model.begintime = DateTime.Parse(ds.Tables[0].Rows[0]["begintime"].ToString());
                }
                if (ds.Tables[0].Rows[0]["endtime"].ToString() != "")
                {
                    model.endtime = DateTime.Parse(ds.Tables[0].Rows[0]["endtime"].ToString());
                }
                return model;
            }
            else
            {
                return null;
            }
        }

        public bool Update(Model.advertisement model)
        {
            using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        StringBuilder strSql = new StringBuilder();
                        strSql.Append("update  "+ databaseprefix+"advertisement  set ");
                        strSql.Append("wid=@wid,");
                        strSql.Append("title=@title,");
                        strSql.Append("categoryid=@categoryid,");
                        strSql.Append("img_path=@img_path,");
                        strSql.Append("linkurl=@linkurl,");
                        strSql.Append("context=@context,");
                        strSql.Append("beginTime=@beginTime,");
                        strSql.Append("endtime=@endtime,");
                        strSql.Append("isdelete=@isdelete,");
                        strSql.Append("remark=@remark,");
                        strSql.Append("ordernumber=@ordernumber");
                        strSql.Append("  where id=@id");
                        SqlParameter[] parameters = {
                                new SqlParameter("@id", SqlDbType.Int,4),
                                new SqlParameter("@wid", SqlDbType.Int,4),
                                new SqlParameter("@title", SqlDbType.NVarChar,100),
                                new SqlParameter("@categoryid", SqlDbType.Int,4),
                                new SqlParameter("@img_path", SqlDbType.NVarChar,200),
                                new SqlParameter("@linkurl", SqlDbType.NVarChar,200),
                                new SqlParameter("@context", SqlDbType.NVarChar,500),
                                new SqlParameter("@beginTime", SqlDbType.DateTime),
                                new SqlParameter("@endtime", SqlDbType.DateTime),
                                new SqlParameter("@isdelete", SqlDbType.Bit),
                                new SqlParameter("@remark", SqlDbType.NVarChar,500),
                                new SqlParameter("@ordernumber", SqlDbType.Int,4)};
                        parameters[0].Value = model.id;
                        parameters[1].Value = model.wid;
                        parameters[2].Value = model.title;
                        parameters[3].Value = model.categoryid;
                        parameters[4].Value = model.img_path;
                        parameters[5].Value = model.linkurl;
                        parameters[6].Value = model.context;
                        parameters[7].Value = model.begintime;
                        parameters[8].Value = model.endtime;
                        parameters[9].Value = model.isdelete;
                        parameters[10].Value = model.remark;
                        parameters[11].Value = model.ordernumber;
                        DbHelperSQL.ExecuteSql(conn, trans, strSql.ToString(), parameters);
                        trans.Commit();
                        conn.Close();
                        return true;
                    }
                    catch(Exception ex)
                    {
                        return false;
                    }
                }
            }
        }

        public bool delete(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update "+ databaseprefix+"advertisement set isdelete=1 where id=@id");
            SqlParameter[] parameters = {
                    new SqlParameter("@id", SqlDbType.Int,4)};
            parameters[0].Value = id;
            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters)>0? true :false;
        }
        

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top,string where,bool isadminedit)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            string timenow = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            strSql.Append(" id,wid,title,categoryid,img_path,linkurl,context,addtime,beginTime,endtime,isdelete,remark,ordernumber from " + databaseprefix + "Advertisement ");
            strSql.Append(where);
            strSql.Append(" order by ordernumber asc");
            return DbHelperSQL.Query(strSql.ToString());
        }



        public List<Model.advertisement> GetModelList(string strWhere,int num,bool isadminedit)
        {
            List<Model.advertisement> defaulelist = new List<Model.advertisement>();
            string stringtop = "";
            if (num > 0)
            {
                stringtop = "top " + num.ToString(); 
            }

            if (!string.IsNullOrEmpty(strWhere))
            {
                strWhere = "Where " + strWhere;
            }

            using (IDbConnection db = DbFactory.GetOpenedConnection())
            {
                defaulelist = db.Query<Model.advertisement>("Select "+ stringtop + " * From [dbo].[wx_Advertisement] " + strWhere).ToList();
            }
            if (defaulelist.Count == 0 && !isadminedit)
            {
                return GetDefaultModelList();
            }
            return defaulelist;
        }



        public List<Model.advertisement> GetDefaultModelList()
        {
            List<Model.advertisement> list = new List<Model.advertisement>();
            Model.advertisement defaultmodel = new Model.advertisement();
            string defaultpath = System.Configuration.ConfigurationManager.AppSettings["defaultadvertisementurl"];
            defaultmodel.img_path = defaultpath;
            list.Add(defaultmodel);
            return list;
        }


    }
}
